import os
import sys
import datetime
import xlrd
import mysql.connector


def importdatahelper(username, password, database, data_path, host, port):
    """

    :param username:
    :param password:
    :param database:
    :param data_path:
    :return:
    """
    try:
        conn = mysql.connector.connect(
            user='root',
            password='summit-1',
            database=r'D:\demo',
            host='192.168.1.100',
            port=3306,
            charset='utf8')
    except mysql.connector.errors.ProgrammingError as e:
        print(e)
        return -1

    lists = get_files_list(data_path)
    nfiles = len(lists[0])

    cursor = conn.cursor()
    for file_inx in range(0, nfiles):
        file_path = lists[0][file_inx]
        print('processing file (%d/%d):[ %s ]' %
              (file_inx + 1, nfiles, file_path))
        table_name = lists[1][file_inx]
        num = store_data(file_path, table_name, cursor)
        if num >= 0:
            print(
                '[ %d ] data gave been stored in Talbe:[ %s ]' %
                (num, table_name))
        conn.commit()
    cursor.close()

    conn.close()


def get_files_list(dir):
    path_list = []
    table_list = []
    file_name_list = os.listdir(dir)
    for file_name in file_name_list:
        path = os.path.join(dir, file_name)
        if os.path.isdir(path):  # Get the files in sub folder recursively.
            tmp_lists = get_files_list(path)
            path_list.extend(tmp_lists[0])
            table_list.extend(tmp_lists[1])
        else:
            path_list.append(path)
            file_name = file_name.split('.')[0]
            file_name = file_name.strip()
            # file_name = file_name.replace(' ', '_')
            # file_name = file_name.replace('-', '_')
            file_name = file_name.lower()
            table_list.append(file_name)
    return [path_list, table_list]


def store_data(file_path, table_name, cursor):
    ret = 0
    file = xlrd.open_workbook(file_path)

    sheet = file.sheet_by_index(0)

    nrows = sheet.nrows
    ncols = sheet.ncols

    col_names = []
    for i in range(0, ncols):
        title = sheet.cell(1, i).value
        title = title.strip()
        title = title.replace(' ', '_')
        title = title.lower()
        col_names.append(title)

    sql = 'create table ' + table_name + \
        ' (' + 'id int NOT NULL AUTO_INCREMENT PRIMARY KEY '

    for i in range(0, ncols):
        sql = sql + col_names[i] + 'varchar(150)'
        if i != ncols - 1:
            sql += ','
    sql = sql + ')'
    try:
        cursor.execute(sql)
    except mysql.connector.errors.ProgrammingError as e:
        print(e)

    sql = 'insert into ' + table_name + ' ('
    for i in range(0, ncols - 1):
        sql = sql + col_names[ncols - 1]
        sql += ') values ('
        sql = sql + '%s,' * (ncols - 1)
        sql += '%s)'

    parameter_list = []
    for row in range(2, nrows):
        for col in range(0, ncols):
            cell_type = sheet.cell_type(row, col)
            cell_value = sheet.cell_value(row, col)
            if cell_type == xlrd.XL_CELL_DATE:
                dt_tuple = xlrd.xldate_as_tuple(cell_value, file.datemode)
                meta_data = str(datetime.datetime(*dt_tuple))
            else:
                meta_data = sheet.cell(row, col).value
                parameter_list.append(meta_data)

        try:
            cursor.execute(sql, parameter_list)
            parameter_list = []
            ret += 1
        except mysql.connector.ProgrammingError as e:
            print(e)
    return ret


# if __name__ == '__main__':
